package com.cmict.data.form.api.util;

import cn.hutool.core.util.ObjectUtil;
import com.cmict.data.form.api.entity.vo.*;
import com.cmict.data.framework.common.util.date.DateUtils;
import com.cmict.data.framework.security.core.util.SecurityFrameworkUtils;
import com.google.common.annotations.VisibleForTesting;
import org.apache.commons.lang3.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import static com.cmict.data.framework.common.exception.util.ServiceExceptionUtil.exception;
import static com.cmict.data.system.enums.ErrorCodeConstants.TABLE_FIELD_NAME_ERROR;
import static com.cmict.data.system.enums.ErrorCodeConstants.UNSUPPORT_DATA_TYPE_ERROR;

/**
 * @description: sql拼接工具类
 * @author: qiaoyu
 * @create: 2022-03-15 22:57
 **/
public class SqlUtil {

    public static Result tableFieldSplicUtil(List<SysFromTableColumnVo> SysFromTableColumnList, String sql) {
        for (int i = 0; i < SysFromTableColumnList.size(); i++) {
            SysFromTableColumnVo sysFromTableColumnVo = SysFromTableColumnList.get(i);
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getName())){
                sql = sql + ",`" + sysFromTableColumnVo.getName() + "` ";
            }else{
                return Result.failed("字段名必填!",null);
            }
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getFormat())){
                if(sysFromTableColumnVo.getFormat().equals("longtext")){
                    sql = sql + sysFromTableColumnVo.getFormat();
                }else{
                    sql = sql + sysFromTableColumnVo.getFormat() + "(" + sysFromTableColumnVo.getLength() + ")";
                }
            }else{
                return Result.failed("字段类型必填!",null);
            }
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getDesc())){
                sql = sql + " COMMENT '" + sysFromTableColumnVo.getDesc() + "'";
            }else{
                return Result.failed("字段名必填!",null);
            }
        }
        Result r = new Result();
        r.setData(sql);
        return r;
    }

    public static Result addTableColumnUtil(List<SysFromTableColumnVo> SysFromTableColumnList, String sql) {
        for (int i = 0; i < SysFromTableColumnList.size(); i++) {
            SysFromTableColumnVo sysFromTableColumnVo = SysFromTableColumnList.get(i);
            sql = sql + " ADD ";
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getName())){
                sql = sql + sysFromTableColumnVo.getName() + " ";
            }else{
                return Result.failed("字段名必填!");
            }
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getFormat())){
                if("longtext".equals(sysFromTableColumnVo.getFormat())){
                    sql = sql + sysFromTableColumnVo.getFormat();
                }else{
                    sql = sql + sysFromTableColumnVo.getFormat() + "(" + sysFromTableColumnVo.getLength() + ")";
                }
            }else{
                return Result.failed("字段类型必填!");
            }
            if(ObjectUtil.isNotEmpty(sysFromTableColumnVo.getDesc())){
                sql = sql + " COMMENT '" + sysFromTableColumnVo.getDesc() + "',";
            }else{
                return Result.failed("字段名必填!");
            }
        }
        sql = sql.substring(0, sql.length()-1);
        Result r = new Result();
        r.setData(sql);
        return r;
    }


    public static String insertIntoData(SysFromInsertDataVo sysFromInsertDataVo, String sql) {
        String key = " (";
        String value = "value(";
        List<SysFromDataColumnVo> sysFromInsertDataList = sysFromInsertDataVo.getData();
        for (int i = 0; i < sysFromInsertDataList.size(); i++) {
            SysFromDataColumnVo sysFromInsertDataColumnVo = sysFromInsertDataList.get(i);
            key = key + "`"+sysFromInsertDataColumnVo.getName() + "`,";
            if(sysFromInsertDataColumnVo.getType().equals("varchar") || sysFromInsertDataColumnVo.getType().equals("date")  || sysFromInsertDataColumnVo.getType().equals("time") || sysFromInsertDataColumnVo.getType().equals("year") || sysFromInsertDataColumnVo.getType().equals("longtext")){
                value = value + "'"+ sysFromInsertDataColumnVo.getValue() + "',";
            }else if(sysFromInsertDataColumnVo.getType().equals("datetime")){
                Map<Object,Object> val = IsNullUtil.DateChange(sysFromInsertDataColumnVo.getName(),sysFromInsertDataColumnVo.getValue(),sysFromInsertDataVo.getTableName());
                value = value + "'"+ val.get("value") + "',";
            }else if(sysFromInsertDataColumnVo.getType().equals("int") || sysFromInsertDataColumnVo.getType().equals("decimal") || sysFromInsertDataColumnVo.getType().equals("double")){
                String val = IsNullUtil.percentageChange(sysFromInsertDataColumnVo.getName(),sysFromInsertDataColumnVo.getValue(),sysFromInsertDataVo.getTableName());
                value = value + val + "," ;
            }else{
                value = value + sysFromInsertDataColumnVo.getValue() + ",";
            }
        }
        key = key.substring(0, key.length() -1);
        value = value.substring(0, value.length() -1);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String LocalDateTime = dateFormat.format(DateUtils.of(java.time.LocalDateTime.now()));
        String dataType = sysFromInsertDataVo.getDataType();
        Long userId = SecurityFrameworkUtils.getLoginUser().getId();
        Long tenantId = SecurityFrameworkUtils.getLoginUser().getTenantId();
        sql = sql + key + ",create_time,update_time,tenant_id,data_type)" + value + ",'" + LocalDateTime +"','" + LocalDateTime +"'," + tenantId +"," + dataType +")";
        //sql = sql + key + ",user,create_time)"+ SecurityFrameworkUtils.getLoginUser()+"," + value + ",'" +date+"')";
        return sql;
    }

    public static String excelInsertIntoData(SysFromInsertDataVo sysFromInsertDataVo, String sql) {
        String key = " (";
        String value = "value(";
        List<SysFromDataColumnVo> sysFromInsertDataList = sysFromInsertDataVo.getData();
        for (int i = 0; i < sysFromInsertDataList.size(); i++) {
            SysFromDataColumnVo sysFromInsertDataColumnVo = sysFromInsertDataList.get(i);
            key = key + "`"+sysFromInsertDataColumnVo.getName() + "`,";
            if(ObjectUtil.isNotEmpty(sysFromInsertDataColumnVo.getType())){
                String valuedata = sysFromInsertDataColumnVo.getValue();
                if(sysFromInsertDataColumnVo.getType().equals("varchar") || sysFromInsertDataColumnVo.getType().equals("date") || sysFromInsertDataColumnVo.getType().equals("time") || sysFromInsertDataColumnVo.getType().equals("year") || sysFromInsertDataColumnVo.getType().equals("longtext") || sysFromInsertDataColumnVo.getType().equals("text")){
                    if(ObjectUtil.isEmpty(sysFromInsertDataColumnVo.getValue())){
                        valuedata = "";
                    }
                    value = value + "'"+ valuedata + "',";
                }else if(sysFromInsertDataColumnVo.getType().equals("datetime")){
                    if(ObjectUtil.isEmpty(sysFromInsertDataColumnVo.getValue())){
                        valuedata = "";
                    }else{
                        Map<Object,Object> val = IsNullUtil.DateChange(sysFromInsertDataColumnVo.getName(),sysFromInsertDataColumnVo.getValue(),sysFromInsertDataVo.getTableName());
                        valuedata = String.valueOf(val.get("value"));
                        value = value + "'"+ valuedata + "',";
                    }
                }else {
                    if(ObjectUtil.isEmpty(sysFromInsertDataColumnVo.getValue())){
                        valuedata = "0";
                    }
                    value = value + valuedata + ",";
                }
            }
        }
        key = key.substring(0, key.length() -1);
        value = value.substring(0, value.length() -1);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = dateFormat.format(DateUtils.of(java.time.LocalDateTime.now()));
		String dataType = sysFromInsertDataVo.getDataType();
		Long tenantId = SecurityFrameworkUtils.getLoginUser().getTenantId();
        sql = sql + key + ",create_time,update_time,tenant_id,data_type)" + value + ",'" + date +"','" + date +"'," + tenantId +"," + dataType +")";
        //sql = sql + key + ",user,create_time)"+ SecurityFrameworkUtils.getLoginUser()+"," + value + ",'" +date+"')";
        return sql;
    }

    public static String upData(SysFromUpDataVo sysFromUpDataVo, String sql, String id) {
        List<SysFromDataColumnVo> SysFromDataColumnList = sysFromUpDataVo.getData();
        for (int i = 0; i < SysFromDataColumnList.size(); i++) {
            SysFromDataColumnVo sysFromDataColumn = SysFromDataColumnList.get(i);
            if(sysFromDataColumn.getType().equals("varchar") || sysFromDataColumn.getType().equals("date") || sysFromDataColumn.getType().equals("time") || sysFromDataColumn.getType().equals("year") || sysFromDataColumn.getType().equals("longtext")){
                sql = sql + sysFromDataColumn.getName() + "='" + sysFromDataColumn.getValue() + "'," ;
            }else if(sysFromDataColumn.getType().equals("datetime")){
                Map<Object,Object> val = IsNullUtil.DateChange(sysFromDataColumn.getName(),sysFromDataColumn.getValue(),sysFromUpDataVo.getTableName());
                sql = sql + sysFromDataColumn.getName() + "='" + val.get("value") + "'," ;
            }else {
                sql = sql + sysFromDataColumn.getName() + "=" + sysFromDataColumn.getValue() + "," ;
            }
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String LocalDateTime = dateFormat.format(DateUtils.of(java.time.LocalDateTime.now()));
        if(null == sysFromUpDataVo.getDataType() || "1".equals(sysFromUpDataVo.getDataType()) || "2".equals(sysFromUpDataVo.getDataType())){
            sql = sql + "data_type" + "=" + sysFromUpDataVo.getDataType() + ", update_time = '" + LocalDateTime +"',";
        }else{
            throw exception(UNSUPPORT_DATA_TYPE_ERROR);
        }
        sql = sql.substring(0, sql.length()-1) + " WHERE id=" + Integer.parseInt(id);
        return sql;
    }

    public static String upDataChild(SysFromUpDataVo sysFromUpDataVo, String sql, String id) {
        List<SysFromDataColumnVo> SysFromDataColumnList = sysFromUpDataVo.getData();
        for (int i = 0; i < SysFromDataColumnList.size(); i++) {
            SysFromDataColumnVo sysFromDataColumn = SysFromDataColumnList.get(i);
            if(sysFromDataColumn.getType().equals("varchar") || sysFromDataColumn.getType().equals("date") || sysFromDataColumn.getType().equals("time") || sysFromDataColumn.getType().equals("year") || sysFromDataColumn.getType().equals("longtext")){
                sql = sql + sysFromDataColumn.getName() + "='" + sysFromDataColumn.getValue() + "'," ;
            }else if(sysFromDataColumn.getType().equals("datetime")){
                Map<Object,Object> val = IsNullUtil.DateChange(sysFromDataColumn.getName(),sysFromDataColumn.getValue(),sysFromUpDataVo.getTableName());
                sql = sql + sysFromDataColumn.getName() + "='" + val.get("value") + "'," ;
            }else {
                sql = sql + sysFromDataColumn.getName() + "=" + sysFromDataColumn.getValue() + "," ;
            }
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String LocalDateTime = dateFormat.format(DateUtils.of(java.time.LocalDateTime.now()));
        if(null == sysFromUpDataVo.getDataType() || "1".equals(sysFromUpDataVo.getDataType()) || "2".equals(sysFromUpDataVo.getDataType())){
            sql = sql + "data_type" + "=" + sysFromUpDataVo.getDataType() + ", update_time = '" + LocalDateTime +"',";
        }else{
            throw exception(UNSUPPORT_DATA_TYPE_ERROR);
        }
        sql = sql.substring(0, sql.length()-1) + " WHERE id =" + Integer.parseInt(id);
        return sql;
    }

    public static String jsonSeachData(SysFromSeachDataVo sysFromSeachData, String sql) {
        String sqlSeachData = "";
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getSeachData())){
            if(!sql.contains("WHERE")){
                sql = sql + " WHERE ";
            }
            for (Object map : sysFromSeachData.getSeachData().entrySet()){
                for (Object maptype : sysFromSeachData.getSearchType().entrySet()){
                    if(((Map.Entry)maptype).getKey().equals(((Map.Entry)map).getKey())){
                        //模糊查询
                        if(((Map.Entry)maptype).getValue().equals("like")){
                            if(((Map.Entry)map).getValue().toString().contains("'!#%@$^',")){
                                sqlSeachData = sqlSeachData + " AND  REPLACE(REPLACE( " + ((Map.Entry)map).getKey()  + ",'~A%1','') ,'~A%2','') IN (" + ((Map.Entry)map).getValue() + ")";
                            }else {
                                sqlSeachData = sqlSeachData + " AND  REPLACE(REPLACE( " + ((Map.Entry)map).getKey()  + ",'~A%1','') ,'~A%2','') LIKE ('%" + ((Map.Entry) map).getValue() + "%')";
                            }
                        }
                        //精确查询
                        if(((Map.Entry)maptype).getValue().equals("equals")){
                            sqlSeachData = sqlSeachData + " AND " + ((Map.Entry) map).getKey() + " = ('" + ((Map.Entry) map).getValue() + "')";
                        }
                        //日期区间查询
                        if(((Map.Entry)maptype).getValue().equals("between")){
                            List<String> dateList = Arrays.asList(((Map.Entry) map).getValue().toString().split(","));
                            String start = intervalDate(dateList.get(0));
                            String end = intervalDate(dateList.get(1));
                            sqlSeachData = sqlSeachData + " AND " + ((Map.Entry) map).getKey() + " between '"+ start + "' and '" + end  + "'";
                        }
                        //区间时间查询-----------待定
                       /* if(((Map.Entry)maptype).getValue().equals("between")){
                            List<String> dateList = Arrays.asList(((Map.Entry) map).getValue().toString().split(","));
                            if(dateList.size()==2){
                                sqlSeachData = sqlSeachData + " AND create_time between '"+ dateList.get(0) + "' and '" + dateList.get(1)  + "'";
                            }
                        }*/
                    }
                }
            }
        }
        String str = " AND ";
        //sqlSeachData = sqlSeachData.replaceFirst(str, "");
        sql = sql + sqlSeachData;
        return sql;
    }

    public static String jsonSeachDate(SysFromSeachDataVo sysFromSeachData, String sql) {
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getStartDate()) && ObjectUtil.isNotEmpty(sysFromSeachData.getEndDate())){
            String startTime = sysFromSeachData.getStartDate();
            String endTime = sysFromSeachData.getEndDate();
            if(!StringUtils.containsIgnoreCase(sql,"WHERE")){
                sql = sql + " WHERE ";
                sql = sql + " create_time between '"+ startTime + "' and '" + endTime + "'";
            }else{
                sql = sql + " AND create_time between '"+ startTime + "' and '" + endTime + "'";
            }
        }
        return sql;
    }

    public static String jsonOederBy(SysFromSeachDataVo sysFromSeachData, String sql) {
        if("all".equals(sysFromSeachData.getDataType())){
            sql = sql + " ORDER BY id desc";
        }else{
            if(ObjectUtil.isNotEmpty(sysFromSeachData.getOrder()) && ObjectUtil.isNotEmpty(sysFromSeachData.getFieldName())){
                String fieldName = sysFromSeachData.getFieldName();
                checkFieldName(fieldName);
                String order = sysFromSeachData.getOrder();
                sql = sql + " ORDER BY "+ fieldName +" " + order + "";
            }else{
                sql = sql + " ORDER BY id " + sysFromSeachData.getOrder() +"";
            }
        }
        return sql;
    }

    public static String page(String sql, SysFromSeachDataVo sysFromSeachData) {
        int current = sysFromSeachData.getCurrent();
        int size = sysFromSeachData.getSize();
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getCurrent())){
            current = sysFromSeachData.getCurrent();
        }
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getSize())){
            size = sysFromSeachData.getSize();
        }
        sql = sql + " LIMIT " + current + "," + size + "";
        return sql;
    }

    @VisibleForTesting
    public static void checkFieldName(String name) {
        if (name == null) {
            return;
        }
        if (!match(name)) {
            throw exception(TABLE_FIELD_NAME_ERROR);
        }
    }

    /**
     * 不能有特殊字符
     * @param str
     * @return
     */
    public static Boolean match(String str) {
        String regEx="^[a-zA-Z0-9_]*$";
        Boolean result = Pattern.compile(regEx).matcher(str).matches();
        return result;
    }

    public static String intervalDate(String str) {
        List<String> strList = Arrays.asList(str.split("-"));
        if(strList.size() == 1 && !str.contains(":")){
            str = str + "-01-01 00:00:00";
        }else if(strList.size() == 2){
            str = str + "-01 00:00:00";
        }else if(strList.size() == 3){
            str = str + " 00:00:00";
        }else{
            return str;
        }
        return str;
    }

}

